package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import model.ConnectDb;
import model.UnidadeFederativa;

public class UFJDBCBuscar {
	private final String COL_NMUF = "nmuf";
	private final String COL_SGUF = "sguf";

	private ConnectDb connectdb = new ConnectDb();
	String schema = connectdb.getSchema_security();
	String user = connectdb.getUser();
	String pass = connectdb.getPass();

	Connection conexao;

	public UFJDBCBuscar(Connection conexao) {
		this.conexao = conexao;
	}

	public UnidadeFederativa ufBuscar(String ufnmuf) {

		UnidadeFederativa unidadefederativa = new UnidadeFederativa();
		String sql = "Select CDUF, NMUF, SGUF from UF where NMUF = '?' order by NMUF";

		PreparedStatement pstmt;

		try {
			PersistConexao persistconexao = new PersistConexao();
			conexao = persistconexao.openConecao(schema, user, pass);

			pstmt = this.conexao.prepareStatement(sql);
			pstmt.setString(1, ufnmuf);
			ResultSet rs = pstmt.executeQuery();

			if (rs.next()) {
				unidadefederativa.setNmuf(rs.getString(COL_NMUF));
				unidadefederativa.setSguf(rs.getString(COL_SGUF));
				System.out.println("*******" + rs);
			}
			// persistconexao.closeConexao();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return unidadefederativa;
	}

	public List<UnidadeFederativa> listarTodos() {

		List<UnidadeFederativa> ufederativa = new ArrayList<UnidadeFederativa>();
		String sql = "Select CDUF, NMUF, SGUF from UF order by NMUF";

		PreparedStatement pstmt;

		try {
			PersistConexao persistconexao = new PersistConexao();
			conexao = persistconexao.openConecao(schema, user, pass);

			pstmt = this.conexao.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();

			while (rs.next()) {

				UnidadeFederativa unidadef = new UnidadeFederativa();
				unidadef.setNmuf(rs.getString(COL_NMUF));
				unidadef.setSguf(rs.getString(COL_SGUF));
				ufederativa.add(unidadef);
			}
			persistconexao.closeConexao();
		} catch (Exception e) {
			e.printStackTrace();
		}

		return ufederativa;
	}

}